/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IUserRightData.
	/// </summary>
	internal sealed class UserRightData : IUserRightData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new user right.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_user_right (user_id, right_id, enable_flag) " +
			"VALUES(@user_id, @right_id, @enable_flag)";

		/// <summary>
		/// The SQL to delete a user right.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_user_right " +
			"WHERE user_id = @user_id AND right_id = @right_id";

		/// <summary>
		/// The SQL to update a user right.
		/// </summary>
		private static readonly string SQL_UPDATE = 
			"UPDATE cg_security_user_right " +
			"SET enable_flag = @enable_flag " +
			"WHERE user_id = @user_id AND right_id = @right_id";

		/// <summary>
		/// The SQL to find a list of rights by user.
		/// </summary>
		private static readonly string SQL_FIND_BY_USER = 
			"SELECT a.user_id, a.right_id, a.enable_flag, b.right_name, b.right_description " +
			"FROM cg_security_user_right a, cg_security_right b " +
			"WHERE b.right_id = a.right_id AND a.user_id = @user_id " +
			"ORDER BY b.right_name";

		/// <summary>
		/// The SQL to find a list of users by right.
		/// </summary>
		private static readonly string SQL_FIND_BY_RIGHT = 
			"SELECT a.user_id, a.right_id, a.enable_flag, b.user_name, b.create_date, b.last_login " +
			"FROM cg_security_user_right a, cg_security_user b " +
			"WHERE b.user_id = a.user_id AND a.right_id = @right_id " +
			"ORDER BY b.user_name";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_USER_ID = "@user_id";
		private static readonly string PARM_RIGHT_ID = "@right_id";
		private static readonly string PARM_ENABLE_FLAG = "@enable_flag";

		#endregion
		
		// ******************************************************************
		// IUserRightData implementation.
		// ******************************************************************

		#region IUserRightData implementation.

		/// <summary>
		/// Creates a new association between a user and a right.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="rightID">The identifier for the right.</param>
		/// <param name="enableFlag">A flag that decides whether to add
		/// or remove the specified right from the effective rights list.</param>
		public void Create(
			int userID,
			int rightID,
			bool enableFlag
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_ENABLE_FLAG, OleDbType.Boolean)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;
			parms[1].Value = rightID;
			parms[2].Value = enableFlag;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString, 
				CommandType.Text, 
				SQL_CREATE, 
				parms
				);

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes an association between a user and a right.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="rightID">The identifier for the right.</param>
		public void Delete(
			int userID,
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;
			parms[1].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Updates an association between a user and a right.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="rightID">The identifier for the right.</param>
		/// <param name="enableFlag">A flag that decides whether to add
		/// or remove the specified right from the effective rights list.</param>
		public void Update(
			int userID,
			int rightID,
			bool enableFlag
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_UPDATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ENABLE_FLAG, OleDbType.Boolean),
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)					
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_UPDATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = enableFlag;
			parms[1].Value = userID;
			parms[2].Value = rightID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_UPDATE, 
				parms
				);

		} // End Update()

		// ******************************************************************

		/// <summary>
		/// Returns a list of all the rights that are explicitly associated 
		/// with the specified user. (Note: this list will not include any 
		/// rights granted through a users roles - for that list use the 
		/// SecurityManager.EffectiveRight method.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <returns>A DataSet containing all the rights that are associated
		/// with the specified user.</returns>
		public System.Data.DataSet FindByUser(
			int userID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_USER
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_USER, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_USER, 
				parms
				);

		} // End FindByUser()

		// ******************************************************************

		/// <summary>
		/// Returns a list of all the users that have been explicitly associated 
		/// with the specified right.
		/// </summary>
		/// <param name="userID">The identifier for the right.</param>
		/// <returns>A DataSet containing all the users that are associated
		/// with the specified right.</returns>
		public System.Data.DataSet FindByRight(
			int rightID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_RIGHT
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_RIGHT_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_RIGHT, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = rightID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_RIGHT, 
				parms
				);

		} // End FindByRight()

		#endregion 

	} // End class UserRightData

} // End namespace CG.Security.Data.Access

